package com.fishingwithme.infrastructure.mapper.circle;

import com.fishingwithme.infrastructure.dtos.circle.CityDto;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface CityMapper {
    @Select("SELECT city_code as cityCode, city FROM city_code ORDER BY city_code")
    List<CityDto> selectAllCities();
    
    @Select("SELECT city_code as cityCode, city FROM city_code ORDER BY city_code")
    IPage<CityDto> selectCitiesByPage(Page<CityDto> page);
    
    @Select("SELECT city_code as cityCode, city FROM city_code WHERE city LIKE CONCAT('%', #{cityName}, '%') ORDER BY city_code")
    IPage<CityDto> selectCitiesByPageAndSearch(Page<CityDto> page, @Param("cityName") String cityName);

    @Select("SELECT DISTINCT city, city_code FROM spot s " +
            "WHERE city IS NOT NULL AND city_code IS NOT NULL " +
            "AND NOT EXISTS (SELECT 1 FROM city_code c WHERE c.city_code = s.city_code) " +
            "ORDER BY city_code LIMIT 1000")
    List<CityDto> selectDistinctCitiesFromSpot();

    @Select("SELECT COUNT(*) FROM spot s " +
            "WHERE city IS NOT NULL AND city_code IS NOT NULL " +
            "AND NOT EXISTS (SELECT 1 FROM city_code c WHERE c.city_code = s.city_code)")
    int countPendingCities();

    @Insert("<script>" +
            "INSERT IGNORE INTO city_code (city_code, city) VALUES " +
            "<foreach collection='list' item='item' separator=','>" +
            "(#{item.cityCode}, #{item.city})" +
            "</foreach>" +
            "</script>")
    int batchInsertCities(@Param("list") List<CityDto> cities);

    @Insert("INSERT IGNORE INTO city_code (city_code, city) VALUES (#{cityCode}, #{city})")
    int insertCityIgnoreDuplicate(CityDto cityDto);

    @Select("SELECT COUNT(*) FROM city_code")
    int countCities();

    @Delete("DELETE FROM city_code")
    int deleteAllCities();

    @Select("SELECT city_code as cityCode, city FROM city_code WHERE city LIKE CONCAT('%', #{keyword}, '%') ORDER BY city LIMIT 20")
    List<CityDto> searchCitiesByName(String keyword);
}